package cool.xiaokang.familyaccount.dao.impl;

import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;

import cool.xiaokang.familyaccount.dao.InAccountDao;
import cool.xiaokang.familyaccount.dao.base.impl.BaseDaoImpl;
import cool.xiaokang.familyaccount.pojo.InAccount;
import cool.xiaokang.familyaccount.utils.DateFormateUtil;

@Repository
public class InAccountDaoImpl extends BaseDaoImpl<InAccount> implements InAccountDao {

	// 根据用户id和账单类型分类，查询当前年或某年收入账单金额总和
	@SuppressWarnings("unchecked")
	public List<Object> findInAccountGroupByInAccountTypeName(Long user_id, String year) {
		String hql = "";
		if (StringUtils.isNotBlank(year)) {
			hql = "SELECT iat.inaccounttype_name,SUM(ia.inaccount_money) FROM InAccount ia LEFT JOIN ia.inAccountType iat LEFT JOIN iat.user u where u.user_id=? AND YEAR(ia.inaccount_datetime)=YEAR(?) GROUP BY iat.inaccounttype_name";
			return (List<Object>) this.getHibernateTemplate().find(hql, user_id, DateFormateUtil.transferYear(year));
		} else {
			hql = "SELECT iat.inaccounttype_name,SUM(ia.inaccount_money) FROM InAccount ia LEFT JOIN ia.inAccountType iat LEFT JOIN iat.user u where u.user_id=? AND YEAR(ia.inaccount_datetime)=YEAR(NOW()) GROUP BY iat.inaccounttype_name";
			return (List<Object>) this.getHibernateTemplate().find(hql, user_id);
		}
	}

	// 查询当月收入总额
	@SuppressWarnings("rawtypes")
	public Double findCurrentMonthTotalMoney(Long user_id) {
		String hql = "SELECT SUM(ia.inaccount_money) FROM InAccount ia  WHERE ia.user.user_id=? AND MONTH(inaccount_datetime)=MONTH(NOW())";
		List list = this.getHibernateTemplate().find(hql, user_id);
		if (list != null && list.size() > 0) {
			Double money = (Double) list.get(0);
			return money;
		}
		return null;
	}

	// 查询今年或某年每个月的平均收入
	@SuppressWarnings("unchecked")
	public List<Object> findPerMonthAvgMoney(Long user_id, String year) {
		String hql = "";
		if (StringUtils.isNotBlank(year)) {
			hql = "SELECT "
					+ "AVG((case MONTH(ia.inaccount_datetime) when '1' then ia.inaccount_money else 0 end)) as Jan,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '2' then ia.inaccount_money else 0 end)) as Feb,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '3' then ia.inaccount_money else 0 end)) as Mar,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '4' then ia.inaccount_money else 0 end)) as Apr,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '5' then ia.inaccount_money else 0 end)) as May,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '6' then ia.inaccount_money else 0 end)) as Jun,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '7' then ia.inaccount_money else 0 end)) as Jul,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '8' then ia.inaccount_money else 0 end)) as Aug,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '9' then ia.inaccount_money else 0 end)) as Sep,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '10' then ia.inaccount_money else 0 end)) as Oct,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '11' then ia.inaccount_money else 0 end)) as Nov,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '12' then ia.inaccount_money else 0 end)) as Dec"
					+ " FROM InAccount ia  WHERE ia.user.user_id=? AND YEAR(ia.inaccount_datetime)=YEAR(?) GROUP BY MONTH(ia.inaccount_datetime)";
			return (List<Object>) this.getHibernateTemplate().find(hql, user_id, DateFormateUtil.transferYear(year));
		} else {
			hql = "SELECT "
					+ "AVG((case MONTH(ia.inaccount_datetime) when '1' then ia.inaccount_money else 0 end)) as Jan,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '2' then ia.inaccount_money else 0 end)) as Feb,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '3' then ia.inaccount_money else 0 end)) as Mar,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '4' then ia.inaccount_money else 0 end)) as Apr,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '5' then ia.inaccount_money else 0 end)) as May,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '6' then ia.inaccount_money else 0 end)) as Jun,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '7' then ia.inaccount_money else 0 end)) as Jul,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '8' then ia.inaccount_money else 0 end)) as Aug,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '9' then ia.inaccount_money else 0 end)) as Sep,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '10' then ia.inaccount_money else 0 end)) as Oct,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '11' then ia.inaccount_money else 0 end)) as Nov,"
					+ "AVG((case MONTH(ia.inaccount_datetime) when '12' then ia.inaccount_money else 0 end)) as Dec"
					+ " FROM InAccount ia  WHERE ia.user.user_id=? AND YEAR(ia.inaccount_datetime)=YEAR(NOW()) GROUP BY MONTH(ia.inaccount_datetime)";
			return (List<Object>) this.getHibernateTemplate().find(hql, user_id);
		}
	}

	// 根据用户id和账单类型分类，查询当前年或某年，各月的收入账单总额
	@SuppressWarnings("unchecked")
	public List<Object> findInAccountWithYearly(Long user_id, String year) {
		String hql = "";
		if (StringUtils.isNotBlank(year)) {
			hql = "SELECT iat.inaccounttype_name,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '1' then ia.inaccount_money else 0 end)) as Jan,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '2' then ia.inaccount_money else 0 end)) as Feb,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '3' then ia.inaccount_money else 0 end)) as Mar,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '4' then ia.inaccount_money else 0 end)) as Apr,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '5' then ia.inaccount_money else 0 end)) as May,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '6' then ia.inaccount_money else 0 end)) as Jun,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '7' then ia.inaccount_money else 0 end)) as Jul,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '8' then ia.inaccount_money else 0 end)) as Aug,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '9' then ia.inaccount_money else 0 end)) as Sep,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '10' then ia.inaccount_money else 0 end)) as Oct,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '11' then ia.inaccount_money else 0 end)) as Nov,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '12' then ia.inaccount_money else 0 end)) as Dec"
					+ " FROM InAccount ia LEFT JOIN ia.inAccountType iat LEFT JOIN iat.user u where u.user_id=? AND YEAR(ia.inaccount_datetime)=YEAR(?) GROUP BY iat.inaccounttype_name";
			return (List<Object>) this.getHibernateTemplate().find(hql, user_id, DateFormateUtil.transferYear(year));
		} else {
			hql = "SELECT iat.inaccounttype_name,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '1' then ia.inaccount_money else 0 end)) as Jan,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '2' then ia.inaccount_money else 0 end)) as Feb,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '3' then ia.inaccount_money else 0 end)) as Mar,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '4' then ia.inaccount_money else 0 end)) as Apr,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '5' then ia.inaccount_money else 0 end)) as May,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '6' then ia.inaccount_money else 0 end)) as Jun,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '7' then ia.inaccount_money else 0 end)) as Jul,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '8' then ia.inaccount_money else 0 end)) as Aug,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '9' then ia.inaccount_money else 0 end)) as Sep,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '10' then ia.inaccount_money else 0 end)) as Oct,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '11' then ia.inaccount_money else 0 end)) as Nov,"
					+ "SUM((case MONTH(ia.inaccount_datetime) when '12' then ia.inaccount_money else 0 end)) as Dec"
					+ " FROM InAccount ia LEFT JOIN ia.inAccountType iat LEFT JOIN iat.user u where u.user_id=? AND YEAR(ia.inaccount_datetime)=YEAR(NOW()) GROUP BY iat.inaccounttype_name";
			return (List<Object>) this.getHibernateTemplate().find(hql, user_id);
		}
	}
}
